package cqcvc.edu.cn.dao;



import cqcvc.edu.cn.base.BaseDao;
import cqcvc.edu.cn.base.Util;
import cqcvc.edu.cn.entity.MemberInfo;

import java.sql.*;

public class MemberDao {

    public void Insert(MemberInfo memberInfo) throws SQLException {
        Connection connection = BaseDao.getConn();

        try{
            Statement state = connection.createStatement();//容器
            String sql = "insert into member_info (MEM_USERNAME, MEM_PASSWORD, LEVEL_ID, MEM_NAME, MEM_AGE, MEM_SEX, MEM_ADDRESS, MEM_TEL, MEM_PHONE, MEM_EMAIL, REG_TIME, CARD_NO, STATUS, MEM_SCORE,MEM_PIC) value ('" +
                    memberInfo.getMemUsername() + "','"+
                    memberInfo.getMemPassword() + "','" +
                    memberInfo.getMemberLevel().getId() + "','" +
                    memberInfo.getMemName() + "','" +
                    memberInfo.getMemAge() + "','" +
                    memberInfo.getMemSex() + "','" +
                    memberInfo.getMemAddress() + "','" +
                    memberInfo.getMemTel() + "','" +
                    memberInfo.getMemPhone() + "','" +
                    memberInfo.getMemEmail() + "','" +
                    Util.changeDate(memberInfo.getRegTime()) + "','" +
                    memberInfo.getCardNo() + "','" +
                    memberInfo.getStatus() + "','" +
                    memberInfo.getMemScore() + "','" +
                    memberInfo.getMemPic() + "')";

            state.executeUpdate(sql);

        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }finally {
            //connection.close();
        }

    }

    public void update(MemberInfo memberInfo) throws SQLException {
        Connection connection = BaseDao.getConn();

        try{
            Statement state = connection.createStatement();//容器
            String sql = "update member_info set MEM_NAME='" +
                    memberInfo.getMemName() + "',MEM_AGE='" +
                    memberInfo.getMemAge() + "',MEM_SEX='" +
                    memberInfo.getMemSex() + "',MEM_ADDRESS='" +
                    memberInfo.getMemAddress() + "',MEM_EMAIL='" +
                    memberInfo.getMemEmail() + "',MEM_PHONE='" +
                    memberInfo.getMemPhone() + "',MEM_PASSWORD='" +
                    memberInfo.getMemPassword() + "' where ID='" +
                    memberInfo.getId() + "'";
            state.executeUpdate(sql);

        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }finally {
            //connection.close();
        }

    }

    /**
     * 根据用户名查询用户信息
     * @param userName 用户名
     * @return 用户信息
     * @throws SQLException SQL异常
     */
    public MemberInfo findByName(String userName) throws SQLException {
        Connection connection = BaseDao.getConn();
        try {
            Statement state=connection.createStatement();//容器
            String sql="select * from member_info where MEM_USERNAME = '" + userName + "'";           //sql语句
            ResultSet rs = state.executeQuery(sql);     //将sql语句传至数据库，返回的值为一个字符集用一个变量接收
            MemberInfo memberInfo = getList(rs);
            //connection.close();
            return memberInfo;
        }catch (Exception e){
            throw e;
        }
    }

    public MemberInfo findById(int id) throws SQLException {
        Connection connection = BaseDao.getConn();

        try {
            Statement state=connection.createStatement();//容器
            String sql="select * from member_info where ID = " +id ;           //sql语句
            ResultSet rs = state.executeQuery(sql);     //将sql语句传至数据库，返回的值为一个字符集用一个变量接收
            MemberInfo memberInfo = getList(rs);
            //connection.close();
            return memberInfo;
        }catch (Exception e){
            throw e;
        }
    }

    public MemberInfo getList(ResultSet rs) throws SQLException {
        MemberInfo memberInfo = new MemberInfo();

        while(rs.next()){
            memberInfo.setId(rs.getString(1));
            memberInfo.setMemUsername(rs.getString(2));
            memberInfo.setMemPassword(rs.getString(3));
            memberInfo.setMemName(rs.getString(5));
            memberInfo.setMemAge(rs.getString(6));
            memberInfo.setMemSex(rs.getString(7));
            memberInfo.setMemAddress(rs.getString(8));
            memberInfo.setMemPhone(rs.getString(10));
            memberInfo.setMemEmail(rs.getString(11));
            memberInfo.setMemPic(rs.getBytes("mem_pic"));
        }

        return memberInfo;
    }

    /**
     * 登录验证，根据用户名和密码对比数据库里存的值
     * @param userName 用户名
     * @param password 密码
     * @return
     */
    public Boolean Login(String userName,String password)
    {
        Connection conn= BaseDao.getConn();
        try {
            Statement statement = conn.createStatement();
            //select * from member_info where mem_username='zhangsan' and mem_password='111111'
            String sql = "select * from member_info where mem_username=? and mem_password=?";
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setString(1,userName);
            ps.setString(2,password);
            //通过预编译的PreparedStatement对象执行
            //ps.execute();
            ResultSet rs = ps.executeQuery();
            if(rs!=null && rs.next())
            {
                return true;
            }
            return false;
        }catch (Exception ex)
        {
            return false;
        }
    }
}
